﻿using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using PrintPeceipt.Utility;
using Spire.Xls;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace PrintPeceipt
{
    public class ExcelHelper
    {
        /// <summary>
        /// 生成excel
        /// </summary>
        /// <param name="peceipts"></param>
        /// <param name="filePath"></param>
        public static void CreateExcel(Peceipts peceipts, string filePath)
        {
            //导出Office2007
            IWorkbook workbook = new XSSFWorkbook();
            ////导出Office2003
            //HSSFWorkbook workbook = new HSSFWorkbook();
            string sheetName = "sheet1";
            ISheet sheet = workbook.CreateSheet(sheetName);
            int rowIndex = 0;

            ICellStyle IcStyleHead = workbook.CreateCellStyle();
            IcStyleHead.Alignment = HorizontalAlignment.Center;//水平对齐
            IcStyleHead.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.IsBold = false;
            font.FontHeightInPoints = 30;
            font.FontName = "微软雅黑";
            IcStyleHead.SetFont(font);

            ICellStyle IcStyle = workbook.CreateCellStyle();
            IcStyle.WrapText = true; //自动换行
            IcStyle.Alignment = HorizontalAlignment.Left;//左对齐
            IcStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font1 = workbook.CreateFont();
            font1.FontName = "微软雅黑";
            font1.FontHeightInPoints = 20;
            IcStyle.SetFont(font1);

            ICellStyle IcStyle1 = workbook.CreateCellStyle();
            IcStyle1.WrapText = true; //自动换行
            IcStyle1.Alignment = HorizontalAlignment.Center;//水平对齐
            IcStyle1.VerticalAlignment = VerticalAlignment.Center;
            IcStyle1.BorderBottom = BorderStyle.Thin;
            IcStyle1.BorderLeft = BorderStyle.Thin;
            IcStyle1.BorderRight = BorderStyle.Thin;
            IcStyle1.BorderTop = BorderStyle.Thin;
            IcStyle1.SetFont(font1);
            #region  head

            IRow headerRow0 = null;
            headerRow0 = sheet.CreateRow(rowIndex++);
            headerRow0.Height = 20 * 40;
            ICell ic0 = headerRow0.CreateCell(0);
            ic0.CellStyle = IcStyleHead;
            ic0.SetCellValue(peceipts.Title);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); //合并单元格
            sheet.AddMergedRegion(region);

            IRow headerRow1 = null;
            headerRow1 = sheet.CreateRow(rowIndex++);
            headerRow1.Height = 20 * 36;
            sheet.SetColumnWidth(0, 10 * 256);
            ICell icl = headerRow1.CreateCell(0);
            icl.CellStyle = IcStyle;
            icl.SetCellValue("单号");

            sheet.SetColumnWidth(1, 40 * 256);
            ICell ic2 = headerRow1.CreateCell(1);
            ic2.CellStyle = IcStyle;
            ic2.SetCellValue(peceipts.ReceiptNum);

            sheet.SetColumnWidth(2, 40 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 20 * 256);
            sheet.SetColumnWidth(5, 20 * 256);
            sheet.SetColumnWidth(6, 20 * 256);

            sheet.SetColumnWidth(7, 20 * 256);
            ICell ic4 = headerRow1.CreateCell(7);
            ic4.CellStyle = IcStyle;
            ic4.SetCellValue("订单日期");

            sheet.SetColumnWidth(8, 40 * 256);
            ICell ic5 = headerRow1.CreateCell(8);
            ic5.CellStyle = IcStyle;
            ic5.SetCellValue(peceipts.ReceiptDate);


            IRow headerRow2 = null;
            headerRow2 = sheet.CreateRow(rowIndex++);
            headerRow2.Height = 20 * 36;
            ICell icl1 = headerRow2.CreateCell(0);
            icl1.CellStyle = IcStyle;
            icl1.SetCellValue("客户");

            ICell icl2 = headerRow2.CreateCell(1);
            icl2.CellStyle = IcStyle;
            icl2.SetCellValue(peceipts.Customer);

            CellRangeAddress region1 = new CellRangeAddress(2, 2 ,1, 2); //合并单元格
            sheet.AddMergedRegion(region1);

            int length = Encoding.UTF8.GetBytes(peceipts.Customer).Length;
            if (length > 56)
                headerRow2.HeightInPoints = 36 * (length / 56 + 1);

            ICell ic130 = headerRow2.CreateCell(3);
            ic130.CellStyle = IcStyle;
            ic130.SetCellValue("电话");

            ICell ic13 = headerRow2.CreateCell(4);
            ic13.CellStyle = IcStyle;
            ic13.SetCellValue(peceipts.Number);

            CellRangeAddress region2 = new CellRangeAddress(2, 2, 4, 5); //合并单元格
            sheet.AddMergedRegion(region2);

            ICell ic14 = headerRow2.CreateCell(7);
            ic14.CellStyle = IcStyle;
            ic14.SetCellValue("备注");

            ICell ic15 = headerRow2.CreateCell(8);
            ic15.CellStyle = IcStyle;
            ic15.SetCellValue(peceipts.Remarks);

            length = Encoding.UTF8.GetBytes(peceipts.Remarks).Length;
            if (length > 28)
                headerRow2.HeightInPoints = 36 * (length / 28 + 1);

            #endregion

            #region  table 

            int j = 0;
            List<string> lstHead = new List<string>();
            lstHead.Add("序号");
            lstHead.Add("条码");
            lstHead.Add("名称");
            lstHead.Add("规格型号");
            lstHead.Add("单位");
            lstHead.Add("数量");
            lstHead.Add("单价");
            lstHead.Add("合计");
            lstHead.Add("备注");
            IRow headerRow3 = null;
            headerRow3 = sheet.CreateRow(rowIndex++);
            headerRow3.Height = 20 * 36;
            foreach (string head in lstHead)
            {
                ICell ic30 = headerRow3.CreateCell(j);
                ic30.CellStyle = IcStyle1;
                ic30.SetCellValue(head);
                j++;
            }

            foreach (var peceipt in peceipts.PeceiptList)
            {
                int n = 0;
                IRow row = sheet.CreateRow(rowIndex++);
                row.Height = 20 * 36;
                var c1 = row.CreateCell(n++);
                c1.SetCellValue(ConvertToString(peceipt.Num));
                c1.CellStyle = IcStyle1;

                var c2 = row.CreateCell(n++);
                c2.SetCellValue(ConvertToString(peceipt.BarCode));
                c2.CellStyle = IcStyle1;

                var c3 = row.CreateCell(n++);
                c3.SetCellValue(ConvertToString(peceipt.ProductName));
                c3.CellStyle = IcStyle1;

                length = Encoding.UTF8.GetBytes(peceipt.ProductName).Length;
                if (length > 28)
                    row.HeightInPoints = 36 * (length / 28 + 1);

                var c4 = row.CreateCell(n++);
                c4.SetCellValue(ConvertToString(peceipt.Specification));
                c4.CellStyle = IcStyle1;

                var c5 = row.CreateCell(n++);
                c5.SetCellValue(ConvertToString(peceipt.Unit));
                c5.CellStyle = IcStyle1;

                var c6 = row.CreateCell(n++);
                c6.SetCellValue(ConvertToString(peceipt.Count));
                c6.CellStyle = IcStyle1;

                var c7 = row.CreateCell(n++);
                c7.SetCellValue(ConvertToString(peceipt.Price));
                c7.CellStyle = IcStyle1;

                var c8 = row.CreateCell(n++);
                c8.SetCellValue(ConvertToString(peceipt.Total));
                c8.CellStyle = IcStyle1;

                var c9 = row.CreateCell(n++);
                c9.SetCellValue(ConvertToString(peceipt.Remark));
                c9.CellStyle = IcStyle1;
                length = Encoding.UTF8.GetBytes(peceipt.Remark).Length;
                if (length > 28)
                    row.HeightInPoints = 36 * (length / 28 + 1);
            }

            #endregion 

            #region foot
            sheet.CreateRow(rowIndex++); //空一行
            IRow footRow1 = null;
            footRow1 = sheet.CreateRow(rowIndex++);
            footRow1.Height = 20 * 36;
            ICell ic31 = footRow1.CreateCell(1);
            ic31.CellStyle = IcStyle;
            ic31.SetCellValue("送货人");

            ICell ic32 = footRow1.CreateCell(2);
            ic32.CellStyle = IcStyle;
            ic32.SetCellValue(peceipts.Delivery);


            ICell ic33 = footRow1.CreateCell(3);
            ic33.CellStyle = IcStyle;
            ic33.SetCellValue("送货地址");

            ICell ic34 = footRow1.CreateCell(4);
            ic34.CellStyle = IcStyle;
            ic34.SetCellValue(peceipts.Address);

            ICell ic37 = footRow1.CreateCell(7);
            ic37.CellStyle = IcStyle;
            ic37.SetCellValue("收货人");

            ICell ic38 = footRow1.CreateCell(8);
            ic38.CellStyle = IcStyle;
            ic38.SetCellValue(peceipts.Consignee);

            IRow footRow2 = null;
            footRow2 = sheet.CreateRow(rowIndex);
            footRow2.Height = 20 * 36;
            ICell ic41 = footRow2.CreateCell(1);
            ic41.CellStyle = IcStyle;
            ic41.SetCellValue("联系人");

            ICell ic42 = footRow2.CreateCell(2);
            ic42.CellStyle = IcStyle;
            ic42.SetCellValue(peceipts.Contact);

            ICell ic43 = footRow2.CreateCell(3);
            ic43.CellStyle = IcStyle;
            ic43.SetCellValue("联系电话");

            ICell ic44 = footRow2.CreateCell(4);
            ic44.CellStyle = IcStyle;
            ic44.SetCellValue(peceipts.ContactNumber);

            ICell ic47 = footRow2.CreateCell(7);
            ic47.CellStyle = IcStyle;
            ic47.SetCellValue("联系微信");

            ICell ic48 = footRow2.CreateCell(8);
            ic48.CellStyle = IcStyle;
            ic48.SetCellValue(peceipts.Wechat);


            #endregion

            using (FileStream fs = File.Create(filePath))
            {
                workbook.Write(fs);
            }
        }

        /// <summary>
        /// excel生成.bmp图片
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="imagepath">图片后缀.bmp</param>
        public static void ChangeExcelToImage(string excelPath, string imagePath)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(excelPath);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.SaveToImage(imagePath);
            //File.Delete(excelPath); //删除excel
        }

        private static string ConvertToString(object obj)
        {
            if (obj == null)
                return "";
            return obj.ToString().Trim();
        }
    }
}
